﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using StillVoiceCommon;
using TN5Y;
using System.Data;
using System.Text;
using Microsoft.ApplicationBlocks.Data;
using System.Data.SqlClient;

namespace TeamTime.Classes
{
    public class Task : DataTableRow
    {
        public string CompanyProjectTask;
        public string CompanyName;
        public new void load(string id)
        {
            base.load(id);
            System.Data.SqlClient.SqlDataReader dr = SqlHelper.ExecuteReader(TN5Y.Utilities.DBConnectionString, CommandType.Text,
                "select * from ViewTasks where ID = " + @id,
                new SqlParameter("id", id));
            if (dr.Read())
            {
                CompanyName = dr["CompanyName"].ToString();
                CompanyProjectTask = dr["CompanyProjectTask"].ToString();
            }
            dr.Close();
            dr = null;
        }
        public Task()
        {
            this.initClass(Utilities.DBConnectionString, "Task");
        }
    }
    public class Tasks
    {
        internal DataSet search(string searchFor, DateTime fromDate, DateTime toDate, string status, int staffMemberIDAllocatedTo, int companyID, int projectID)
        {
            StringBuilder sql = new StringBuilder("SELECT * FROM ViewTasks where 1=1 ");
            if (searchFor != "")
            {
                sql.Append(" and (TaskName like '%'+@searchFor+'%' or");
                sql.Append(" CompanyProject like '%'+@searchFor+'%' or");
                sql.Append(" Description like '%'+@searchFor+'%' ");

                sql.Append(") ");
            }

            if (fromDate != null)
            {
                sql.Append(" and (CreationDate >= @FromDate or CompletionDate >= @FromDate) ");
            }
            if (toDate != null)
            {
                sql.Append(" and (CreationDate <=  @ToDate or CompletionDate <= @ToDate) ");
            }

            if (status == "Completed")
            {
                sql.Append(" and CompletionDate is not null ");
            }
            else if (status == "Not Completed")
            {
                sql.Append(" and CompletionDate is null ");
            }
            if (staffMemberIDAllocatedTo > 0)
            {
                sql.Append(" and StaffMemberIDAllocatedTo = @staffMemberIDAllocatedTo ");
            }
            if (companyID > 0)
            {
                sql.Append(" and CompanyID = @CompanyID ");
            }
            if (projectID > 0)
            {
                sql.Append(" and ProjectID = @ProjectID ");
            }

            //if (sortExpression != "")
            //{
            //    sql.Append(" order by " + sortExpression);
            //}
            return SqlHelper.ExecuteDataset(TN5Y.Utilities.DBConnectionString, CommandType.Text, sql.ToString(),
                                                new SqlParameter("searchFor", searchFor),
                                                new SqlParameter("FromDate", fromDate),
                                                new SqlParameter("ToDate", toDate),
                                                new SqlParameter("staffMemberIDAllocatedTo", staffMemberIDAllocatedTo),
                                                new SqlParameter("CompanyID", companyID),
                                                new SqlParameter("ProjectID", projectID));

        }


        internal DataSet getTasks(string TaskName)
        {
            string sql = "select ID, CompanyProjectTask from ViewTasks ";
            if (TaskName != "")
            {
                sql += "where TaskName = @TaskName";
            }
            return SqlHelper.ExecuteDataset(TN5Y.Utilities.DBConnectionString, CommandType.Text, sql,
                new SqlParameter("TaskName", TaskName));



        }
        internal DataSet getTasks(bool nonCompletedOnly, bool anyOption)
        {
            string sql = "select ID, CompanyProjectTask from ViewTasks ";
            if (nonCompletedOnly == true)
            {
                sql += "where CompletionDate is null ";
            }
            if (anyOption) { sql = " select 0 as ID,  '<Any>' as Description UNION " + sql; }
            sql += " Order by CompanyProjectTask ";
            return SqlHelper.ExecuteDataset(TN5Y.Utilities.DBConnectionString, CommandType.Text, sql);



        }
        internal DataSet getTimeEntriesFromTaskID(Int32 TaskID)
        {
            string sql = " select * from ViewTimeEntries ";
            if (TaskID > 0)
            {
                sql += " where TaskID = @TaskID ";
            }
            return SqlHelper.ExecuteDataset(TN5Y.Utilities.DBConnectionString, CommandType.Text, sql,
                new SqlParameter("TaskID", TaskID));

        }

        internal int getDuplicateTaskID(string taskName, Int32 projectID, string ID)
        {
            int ret = 0;
            TN5YProject project = new TN5YProject();
            project.load(projectID.ToString());

            string sql = "select ID from ViewTasks where CompanyProjectTask = @CompanyProjectTask  ";
            if (ID != "") { sql += " and ID <> '" + ID + "'"; }//same name different id
            SqlDataReader dr;
            using (dr = SqlHelper.ExecuteReader(TN5Y.Utilities.DBConnectionString, CommandType.Text, sql,
                new SqlParameter("CompanyProjectTask", project.CompanyProject + " - " + taskName)))
            {
                if (dr.Read())
                {
                    ret = Convert.ToInt32(dr[0]);
                }
                dr.Close();
            }
            return ret;
        }



        public string getTaskDescription(int ID)
        {
            string ret = "";
            if (ID != 0)
            {
                string sql = "select Description from ViewTasks where ID = @ID";
                using (SqlDataReader dr = SqlHelper.ExecuteReader(TN5Y.Utilities.DBConnectionString, CommandType.Text, sql, new SqlParameter("ID",ID)))
                {
                    if (dr.Read())
                        ret = dr[0].ToString();

                    dr.Close();
                }
            }
            return ret;
        }
    }
}